package com.telecom.cms.actions;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.telecom.cms.dao.LOC_LATNDAO;
import com.telecom.cms.dao.SEG_BIZ_ATOM_DEFDAO;
import com.telecom.cms.dbconnect.Changelatn;
import com.telecom.cms.dao.GetConnect;

import com.telecom.cms.dto.*;

import com.telecom.cms.util.CurrentTime;
import com.telecom.cms.util.FileOperate;

/**
 * @author yueyingnishang
 * 
 */
public class ScorePickUpAction extends Action {

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.apache.struts.action.Action#execute(org.apache.struts.action.ActionMapping,
	 *      org.apache.struts.action.ActionForm,
	 *      javax.servlet.http.HttpServletRequest,
	 *      javax.servlet.http.HttpServletResponse)
	 */

	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {

		request.setCharacterEncoding("JISAutoDetect");
		Enumeration paramNames = request.getParameterNames();
		Connection con = GetConnect.getCsConnection();
		SEG_BIZ_ATOM_DEFDAO dao = new SEG_BIZ_ATOM_DEFDAO(con);
		List list = new ArrayList();
		String textnum = null;
		String numtype = null;
		String bcy = null;
		String bcm = null;

		String latnid = request.getSession().getAttribute("latnid").toString();
		String latncd = ((String) request.getSession().getAttribute(("latncd")))
				.trim();
		if (latnid.equals("1000")) {
			String latn_id = request.getParameter("selectlatnid");
			LOC_LATNDTO dto = new LOC_LATNDAO().findById(latn_id);
			String latn_cd = dto.getLatn_cd();
			String latn_desc = dto.getLatn_desc();
			latnid = latn_id;
			latncd = latn_cd;
			request.getSession().setAttribute("selectlatnid", latn_id);
			request.getSession().setAttribute("selectlatndesc", latn_desc);
			request.getSession().setAttribute("selectlatncd", latn_cd);
		}
		while (paramNames.hasMoreElements()) {

			String paramName = (String) paramNames.nextElement();
			if (!paramName.equals("Submit")
					&& !paramName.equals("selectlatnid")) {

				if (paramName.equalsIgnoreCase("textarea")) {

					textnum = request.getParameter("textarea");

				}

				else if (paramName.equalsIgnoreCase("radiobutton")) {

					numtype = request.getParameter("radiobutton");

				}

				else if (paramName.equalsIgnoreCase("billing_cycle_y")) {

					bcy = request.getParameter("billing_cycle_y");

				} else if (paramName.equalsIgnoreCase("billing_cycle_m")) {

					bcm = request.getParameter("billing_cycle_m");

				}

			}
		}

		String[] groupname = request.getParameterValues("type");

		for (int i = 0; i < groupname.length; i++) {

			String[] atomvalue = request.getParameterValues(groupname[i]);

			for (int j = 0; j < atomvalue.length; j++) {
				SEG_BIZ_ATOM_DEFDTO dto = new SEG_BIZ_ATOM_DEFDTO();

				int atomid = Integer.parseInt(atomvalue[j]);
				dto = dao.findById(atomid);
				list.add(dto);
			}

		}

		String num[] = textnum.split("\r\n");

		String BILLING_CYCLE_ID = "";
		if (bcm.length() == 1) {

			BILLING_CYCLE_ID = (bcy + "0" + bcm).trim();

		} else {

			BILLING_CYCLE_ID = (bcy + bcm).trim();

		}
		List userlist = new ArrayList();

		String rssql = "select PV_MART_F.BAS_PRD_INST_CUR.Prd_Inst_Id ,PV_MART_F.BAS_PRD_INST_CUR.User_Name ,PV_MART_F.BAS_PRD_INST_CUR.Accs_Nbr ,PV_MART_F.BAS_PRD_INST_CUR.Acct_Id ,PV_MART_F.BAS_PRD_INST_CUR.Cust_Id";
		String endsql = "";

		String tabname = " from ";
		List tablist = new ArrayList();
		tablist = dao.findTabName();

		tabname = tabname + (String) tablist.get(1);

		tabname = tabname + "  left join  " + (String) tablist.get(0) + " on "
				+ (String) tablist.get(1) + ".Prd_Inst_Id = "
				+ (String) tablist.get(0) + ".Prd_Inst_Id  and "
				+ (String) tablist.get(0) + ".BILLING_CYCLE_ID = "
				+ BILLING_CYCLE_ID;

		tabname = tabname + "  left join  " + (String) tablist.get(3) + " on "
				+ (String) tablist.get(1) + ".Prd_Inst_Id = "
				+ (String) tablist.get(3) + ".Prd_Inst_Id  and "
				+ (String) tablist.get(3) + ".Date_Id = "
				+ CurrentTime.getPDate();

		tabname = tabname + "  left join  " + (String) tablist.get(4) + " on "
				+ (String) tablist.get(1) + ".Prd_Inst_Id = "
				+ (String) tablist.get(4) + ".Prd_Inst_Id  and "
				+ (String) tablist.get(4) + ".BILLING_CYCLE_ID = "
				+ BILLING_CYCLE_ID + "  and " + (String) tablist.get(4)
				+ ".Prd_Prom_Stat_Id = 10   ";

		for (int j = 0; j < list.size(); j++) {

			SEG_BIZ_ATOM_DEFDTO dto = (SEG_BIZ_ATOM_DEFDTO) list.get(j);

			if (null == dto.getSRC_TABLE_COL_NAME()
					|| "" == dto.getSRC_TABLE_COL_NAME()||1==dto.getSHOW_TYPE_ID()) {

				if (dto.getBIZ_ATOM_FLAG().equalsIgnoreCase("money")) {

					rssql = rssql + "," + dto.getBAS_TABLE_NAME().trim() + "."
							+ dto.getBAS_TABLE_COL_NAME().trim() + "/100 as "
							+ dto.getBAS_TABLE_COL_NAME().trim();

				} else if (dto.getBIZ_ATOM_FLAG().equalsIgnoreCase("time")) {

					rssql = rssql + "," + dto.getBAS_TABLE_NAME().trim() + "."
							+ dto.getBAS_TABLE_COL_NAME().trim() + "/60.00 as "
							+ dto.getBAS_TABLE_COL_NAME().trim();

				} else {
					rssql = rssql + "," + dto.getBAS_TABLE_NAME().trim() + "."
							+ dto.getBAS_TABLE_COL_NAME().trim();
				}

			} else {

				rssql = rssql + "," + dto.getCDE_SRC_TABLE_NAME().trim() + "."
						+ dto.getSRC_TABLE_COL_NAME().trim();

				if (1 != dto.getSHOW_TYPE_ID()) {

					tabname = tabname + "  left join  "
							+ dto.getCDE_SRC_TABLE_NAME() + " on "
							+ dto.getBAS_TABLE_NAME() + "."
							+ dto.getBAS_TABLE_COL_NAME().trim() + " = "
							+ dto.getCDE_SRC_TABLE_NAME() + "."
							+ dto.getBAS_TABLE_COL_NAME().trim();
				}

			}
		}

		String nowtime = CurrentTime.getCurrentTime2();
		String user_id = (String) request.getSession().getAttribute("userid");
		FileOperate opa = new FileOperate();
		String sql = "";
		PreparedStatement psmt = null;

		if (null == user_id) {

			user_id = "000";
		}

		if (num.length < 1000) {

			String usernumber = "";

			for (int i = 0; i < num.length; i++) {
				if (i == 0) {
					usernumber += ("'" + num[i].trim() + "'");

				} else {
					usernumber += (",'" + num[i].trim() + "'");
				}

			}

			sql = rssql + tabname + endsql + " where "
					+ (String) tablist.get(1) + "." + numtype + " in ("
					+ usernumber + " ) and " + (String) tablist.get(1)
					+ ".Cde_Prd_Inst_Stat_Id = 'F0A' ";

		}

		else {

			psmt = con
					.prepareStatement(" CREATE MULTISET TABLE PD_CMS.NUM"
							+ user_id
							+ nowtime
							+ " ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT( INSERT_VALUE CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC TITLE '号码值')");
			psmt.executeUpdate();

			opa.newFile("D:/cmslog/" + user_id + nowtime + ".txt", textnum
					.replace("\r\n", "|\r\n")
					+ "|");

			System.out
					.println("***************************creat numtxt ok !!************************");

			opa
					.newFile(
							"D:/cmslog/" + user_id + nowtime + ".tpump",
							".LOGTABLE PD_CMS.L"
									+ user_id
									+ nowtime
									+ "LOG;<br> .LOGON dbc/pu_cms,yuancms1271;<br> .begin LOAD  ERRLIMIT 10<br>CHECKPOINT 1 <br>SESSIONS 1 <br>TENACITY 2 <br>pack 10 <br>SERIALIZE ON <br> errortable PD_CMS.E"
									+ user_id
									+ nowtime
									+ "ERO; <br>.layout Lay1; <br>.field insert_value * VARCHAR(30) KEY; <br>.dml label dml1; <br>insert PD_CMS.NUM"
									+ user_id
									+ nowtime
									+ " (insert_value) values ( :insert_value); <br>.import infile \""
									+ "D:/cmslog/"
									+ user_id
									+ nowtime
									+ ".txt\""
									+ "<br>format vartext '|'"
									+ "  <br>layout Lay1  <br>apply dml1;  <br>.end LOAD; <br>.LOGOFF;");

			System.out
					.println("***************************creat controlfile ok !!*********************");

			opa.newFile(user_id + nowtime + ".bat", " tpump < D:/cmslog/"
					+ user_id + nowtime + ".tpump");
			System.out
					.println("***************************creat batfile ok !!***********************");

			try {
				Runtime r = Runtime.getRuntime();
				Process p = r.exec(user_id + nowtime + ".bat");
				BufferedReader bf = new BufferedReader(new InputStreamReader(p
						.getInputStream()));

				String line = null;
				while ((line = bf.readLine()) != null) {
					System.out.println(line);

				}

				System.out
						.println("******************************tpump data ok************************************");

			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			// }

			// con.setAutoCommit(false);
			//
			// PreparedStatement psmt = con
			// .prepareStatement("delete from PD_CMS.SEG_USER_TEMPDB where
			// Inset_Date = "
			// + nowdate);
			// psmt.executeUpdate();
			//
			// for (int i = 0; i < num.length; i++) {
			// if (i == 0) {
			// usernumber += ("'" + num[i].trim() + "'");
			//		 
			// } else {
			// usernumber += (",'" + num[i].trim() + "'");
			// }
			// if (i != 0 && i % 50 == 0) {
			// try {
			// con.commit();
			//
			// } catch (Exception e) {
			//
			// con.rollback();
			//
			// }
			// }
			// psmt = con.prepareStatement("insert into PD_CMS.SEG_USER_TEMPDB
			// values('"
			// + user_id + "','" + nowtime + "','" + nowdate + "','"
			// + num[i].trim() + "')");
			// psmt.executeUpdate();
			//
			// }
			//		   
			// psmt = con.prepareStatement("COLLECT STATISTICS ON
			// PD_CMS.SEG_USER_TEMPDB COLUMN ( User_Id ,Inset_Time)");
			// psmt.executeUpdate();
			sql = rssql + tabname + endsql + " where "
					+ (String) tablist.get(1) + "." + numtype
					+ " in ( select INSERT_VALUE from PD_CMS.NUM" + user_id
					+ nowtime + " ) and " + (String) tablist.get(1)
					+ ".Cde_Prd_Inst_Stat_Id = 'F0A' ";

		}

		sql = Changelatn.changeLatnId(latncd, sql);

		System.out.println(sql);

		psmt = con.prepareStatement(sql);

		ResultSet rset = psmt.executeQuery();

		while (rset.next()) {

			String[] desc = new String[list.size() + 5];

			desc[0] = rset.getString(1);
			desc[1] = rset.getString(2);
			desc[2] = rset.getString(3);
			desc[3] = rset.getString(4);
			desc[4] = rset.getString(5);

			for (int cn = 0; cn < list.size(); cn++) {

				if (rset.getString(cn + 6) == null) {

					desc[cn + 5] = "0";

				} else {
					desc[cn + 5] = rset.getString(cn + 6);
				}
			}

			userlist.add(desc);

		}
		if (rset != null) {

			rset.close();

		}

		if (num.length >= 1000) {
			psmt = con.prepareStatement("drop table PD_CMS.NUM" + user_id
					+ nowtime);

			psmt.execute();
			System.out
					.println("************************** drop tab le ok ******************");
			opa.delFile("D:/cmslog/" + user_id + nowtime + ".txt");
			opa.delFile("D:/cmslog/" + user_id + nowtime + ".tpump");
			opa.delFile(user_id + nowtime + ".bat");
			System.out
					.println("************************   del tempfile ok   *****************");
		}
		if (psmt != null) {

			psmt.close();
		}

		// request.setAttribute("SQL", sql);

		request.getSession().setAttribute("Sqllist", list);
		request.setAttribute("LATNID", latnid);
		request.getSession().setAttribute("RSLIST", userlist);

		return mapping.findForward("success");

		// return mapping.findForward("failure");
	}
}
